package cn.com.chec.en.dao;

import cn.com.chec.en.model.domain.ChannelInfo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

@Repository
public class ChannelInfoDao {
	protected static final Logger log = Logger.getLogger(ChannelInfoDao.class);

	List<ChannelInfo> allChildren = new ArrayList<ChannelInfo>();

	public boolean insert(ChannelInfo info) {
		try {
			String insert = "insert into tbl_channel_i(ch_name,ch_desc,ch_order,ch_parentid,ch_lgid_fk,ch_showtype,ch_image,ch_haschild) values(?,?,?,?,?,?,?,?)";
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);
			PreparedStatement pstmt = conn.prepareStatement(insert);
			int i = 0;
			i++;
			pstmt.setString(i, info.getChannelName());
			i++;
			pstmt.setString(i, info.getChannelDesc());
			i++;
			pstmt.setInt(i, 0);
			i++;
			pstmt.setInt(i, info.getChannelParentId());
			i++;
			pstmt.setInt(i, info.getLanguageId());
			i++;
			pstmt.setInt(i, info.getShowType());
			i++;
			pstmt.setString(i, info.getChannelImage());
			i++;
			pstmt.setInt(i, 0);
			pstmt.execute();

			if (info.getChannelParentId() > 0) {
				String updateSQL = "update tbl_channel_i set ch_haschild = 1 where ch_id = ?";
				PreparedStatement updatePstmt = conn
						.prepareStatement(updateSQL);
				updatePstmt.setInt(1, info.getChannelParentId());
				updatePstmt.executeUpdate();
			}
			conn.commit();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<ChannelInfo> getALL() {
		List<ChannelInfo> list = new ArrayList<ChannelInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_channel_i order by ch_order ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			ChannelInfo info = null;
			while (rs.next()) {
				info = new ChannelInfo();
				info.setChannelId(rs.getInt("ch_id"));
				info.setChannelName(rs.getString("ch_name"));
				info.setChannelDesc(rs.getString("ch_desc"));
				info.setChannelParentId(rs.getInt("ch_parentid"));
				info.setChannelOrder(rs.getInt("ch_order"));
				info.setLanguageId(rs.getInt("ch_lgid_fk"));
				info.setShowType(rs.getInt("ch_showtype"));
				list.add(info);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<ChannelInfo> getSubListByParentIdLanguageId(int languageId,
			int parentId, int start, int num) {
		List<ChannelInfo> list = new ArrayList<ChannelInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_channel_i where ch_parentid = ? "
					+ (languageId > 0 ? " and ch_lgid_fk = ? " : "")
					+ " order by ch_order asc limit ? ,?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setInt(i, parentId);
			if (languageId > 0) {
				i++;
				pstmt.setInt(i, languageId);
			}
			i++;
			pstmt.setInt(i, start);
			i++;
			pstmt.setInt(i, num);
			log.info(pstmt.toString());
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getChannelListByRS(rs));
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getCountByParentIdLanguageId(int languageId, int parentId) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as count from tbl_channel_i where ch_parentid = ? "
					+ (languageId > 0 ? " and ch_lgid_fk = ? " : "");
			PreparedStatement pstmt = conn.prepareStatement(sql);
			int i = 0;
			i++;
			pstmt.setInt(i, parentId);
			if (languageId > 0) {
				i++;
				pstmt.setInt(i, languageId);
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public ChannelInfo getChannelByChannelId(int channelId) {
		ChannelInfo info = new ChannelInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_channel_i where ch_id = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, channelId);
			ResultSet rs = pstmt.executeQuery();
			List<ChannelInfo> list = getChannelListByRS(rs);
			if ((list != null) && (list.size() > 0)) {
				info = (ChannelInfo) list.get(0);
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return info;
	}

	public ChannelInfo getChannelByChannelName(String channelName) {
		ChannelInfo info = new ChannelInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_channel_i where ch_name = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, channelName);
			ResultSet rs = pstmt.executeQuery();
			List<ChannelInfo> list = getChannelListByRS(rs);
			if ((list != null) && (list.size() > 0)) {
				info = (ChannelInfo) list.get(0);
			}
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return info;
	}

	public boolean sort(String[] checkIds) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("update tbl_channel_i set ch_order = ? where ch_id = ?");
			int i = 0;
			for (String id : checkIds) {
				i++;
				pstmt.setInt(1, i);
				pstmt.setInt(2, Integer.valueOf(id).intValue());
				pstmt.addBatch();
			}
			pstmt.executeBatch();
			log.info("[channel sort]" + pstmt.toString());
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean delete(int channelId) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);
			PreparedStatement pstmt = conn
					.prepareStatement("delete from tbl_channel_i where ch_id = ?");
			pstmt.setInt(1, channelId);
			pstmt.execute();

			ChannelInfo info = getChannelByChannelId(channelId);
			int brothers = getCountByParentIdLanguageId(info.getLanguageId(),
					info.getChannelParentId());
			if (brothers == 1) {
				String updateSQL = "update tbl_channel_i set ch_haschild = 0 where ch_id = ?";
				PreparedStatement updatePstmt = conn
						.prepareStatement(updateSQL);
				updatePstmt.setInt(1, info.getChannelParentId());
				updatePstmt.executeUpdate();
			}
			conn.commit();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean update(ChannelInfo info) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("update tbl_channel_i set ch_name = ? ,ch_desc = ? "
							+ (StringUtils.isNotBlank(info.getChannelImage()) ? ",ch_image = ? "
									: "") + " where ch_id = ?");
			int i = 0;
			i++;
			pstmt.setString(i, info.getChannelName());
			i++;
			pstmt.setString(i, info.getChannelDesc());
			if (StringUtils.isNotBlank(info.getChannelImage())) {
				i++;
				pstmt.setString(i, info.getChannelImage());
			}
			i++;
			pstmt.setInt(i, info.getChannelId());
			pstmt.executeUpdate();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public Map<Integer, String> getAllChannelAsMap() {
		Map<Integer, String> map = new HashMap<Integer, String>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_channel_i ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				map.put(Integer.valueOf(rs.getInt("ch_id")),
						rs.getString("ch_name"));
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return map;
	}

	public List<ChannelInfo> getAllChannelAsListByLanguageId(int languageId) {
		List<ChannelInfo> list = new ArrayList<ChannelInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_channel_i where ch_lgid_fk = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, languageId);
			ResultSet rs = pstmt.executeQuery();
			list.addAll(getChannelListByRS(rs));
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getChildrenCount(int parentId, int languageId) {
		int count = 0;
		String hasChild = "select count(1) as count from tbl_channel_i where ch_parentid = ? and ch_lgid_fk = ?";
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn.prepareStatement(hasChild);
			int i = 0;
			i++;
			pstmt.setInt(i, parentId);
			if (languageId > 0) {
				i++;
				pstmt.setInt(i, languageId);
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public List<ChannelInfo> getChannelChildren(int languageId, int parentId) {
		List<ChannelInfo> list = getSubListByParentIdLanguageId(languageId,
				parentId, 0, 100000);
		for (ChannelInfo ci : list) {
			if (ci.getHasChild() == 0)
				this.allChildren.add(ci);
			else {
				getChannelChildren(languageId, ci.getChannelId());
			}
		}
		return this.allChildren;
	}

	public List<ChannelInfo> getChannelChildrenAndSelf(int languageId,
			int parentId) {
		List<ChannelInfo> list = getSubListByParentIdLanguageId(languageId,
				parentId, 0, 100000);
		return digui(list, languageId);
	}

	public List<ChannelInfo> digui(List<ChannelInfo> list, int languageId) {
		List<ChannelInfo> result = new ArrayList<ChannelInfo>();
		for (ChannelInfo ci : list) {
			if (ci.getHasChild() == 0) {
				result.add(ci);
			} else {
				result.add(ci);
				result.addAll(getChannelChildren(languageId, ci.getChannelId()));
			}
		}
		return result;
	}

	public String printChildren(int languageId, int parentId, int removeId) {
		StringBuilder sb = new StringBuilder();
		List<ChannelInfo> list = getChannelChildrenAndSelf(languageId, parentId);
		List<ChannelInfo> reList = getChannelChildrenAndSelf(languageId,
				removeId);
		List<Integer> reIds = new ArrayList<Integer>();
		for (ChannelInfo c : reList) {
			reIds.add(Integer.valueOf(c.getChannelId()));
		}
		reIds.add(Integer.valueOf(removeId));
		for (ChannelInfo ci : list) {
			if (!reIds.contains(Integer.valueOf(ci.getChannelId()))) {
				sb.append(",{id:" + ci.getChannelId() + ", pId:"
						+ ci.getChannelParentId() + ", name:\""
						+ ci.getChannelName() + "\"}" + "\r\n");
			}
		}
		return sb.toString();
	}

	protected List<ChannelInfo> getChannelListByRS(ResultSet rs) {
		List<ChannelInfo> list = new ArrayList<ChannelInfo>();
		ChannelInfo info = null;
		try {
			while (rs.next()) {
				info = new ChannelInfo();
				info.setChannelId(rs.getInt("ch_id"));
				info.setChannelName(rs.getString("ch_name"));
				info.setChannelDesc(rs.getString("ch_desc"));
				info.setChannelParentId(rs.getInt("ch_parentid"));
				info.setChannelOrder(rs.getInt("ch_order"));
				info.setLanguageId(rs.getInt("ch_lgid_fk"));
				info.setShowType(rs.getInt("ch_showtype"));
				info.setChannelImage(rs.getString("ch_image"));
				info.setHasChild(rs.getInt("ch_haschild"));

				list.add(info);
			}
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public boolean isContains(List<ChannelInfo> list, int channelId) {
		for (ChannelInfo ci : list) {
			if (ci.getChannelId() == channelId) {
				return true;
			}
		}
		return false;
	}

	public List<ChannelInfo> getChildrenByChannelId(int languageId,
			int channelId) {
		List<ChannelInfo> resultList = new ArrayList<ChannelInfo>();
		List<ChannelInfo> list = getSubListByParentIdLanguageId(languageId,
				channelId, 0, 2147483647);
		resultList.addAll(list);

		for (ChannelInfo channInfo : list) {
			if (channInfo.getHasChild() == 1) {
				resultList.addAll(getChildrenRecurve(languageId,
						channInfo.getChannelId()));
			}
		}
		return resultList;
	}

	private List<ChannelInfo> getChildrenRecurve(int languageId, int channelId) {
		return getSubListByParentIdLanguageId(languageId, channelId, 0,
				2147483647);
	}

	public static void main(String[] args) {
		ChannelInfoDao dao = new ChannelInfoDao();
		List<ChannelInfo> resultList = dao.getChildrenByChannelId(1, 31);
		for (ChannelInfo info : resultList)
			log.info(info.toString());
	}
}